I am doing a query with a 3-way join. The join and select are working
fine. However, what I want is to select only the row with the
smallest timediff for each distinct t1.date.
This is the query (simplified):
select t1.date, t1.parent,
t1.id, t2.id, t3.id, t3.date,
(t3.date - t1.date) as timediff
from (t1 LEFT JOIN t2
ON t1.parent = t2.id)
LEFT JOIN t3 ON t2.page = t3.page
where
t3.date < t1.date and
t3.event_type = 'page' and
t1.user_id = '61516' and
order by t1.date, timediff;
Here are my results:
t1.date | parent | t1.id | t2.id | t3.id |
t3.date | timediff
------------------------+--------+--------+--------+--------+---------
---------------+----------
2002-11-14 14:46:33-08 | 102846 | 100918 | 102846 | 102845 |
2002-11-14 14:46:11-08 | 00:00:22
2002-11-14 15:34:01-08 | 102856 | 100921 | 102856 | 102855 |
2002-11-14 15:33:50-08 | 00:00:11
2002-11-14 15:34:01-08 | 102856 | 100921 | 102856 | 102853 |
2002-11-14 15:33:40-08 | 00:00:21
2002-11-14 15:34:01-08 | 102856 | 100921 | 102856 | 102847 |
2002-11-14 14:46:35-08 | 00:47:26
2002-11-14 15:34:01-08 | 102856 | 100921 | 102856 | 102845 |
2002-11-14 14:46:11-08 | 00:47:50
(5 rows)
What I want are only the first two rows. However, I don't know how
many distinct t1.date values I will have. Using DISTINCT doesn't seem
to change the output, and I'm not convinced it would keep the correct
row if it did.
My normal habit is to clean up the results in perl, but it seems to
me that I should be able to do it in the SQL query and be a bit
cleaner.
Thank you for your assistance.
Elaine Lindelef